from flask import Blueprint, render_template, request, redirect, url_for, flash, g

# 安装PyMysq包解决  ModuleNotFoundError: No module named 'MySQLdb' 报错问题
# 以下两行代码需要添加
import pymysql
pymysql.install_as_MySQLdb()

from sqlalchemy import text

from createData import Create_data
from exts import db
from models import AdminModel, BookModel, Book_statusModel,BorrowerModel
from blueprints.forms import BookForm

bp = Blueprint("base", __name__, url_prefix="/")


@bp.route("/", methods=["GET", "POST"])
@bp.route("/index")
def index():
    return render_template("index.html")
@bp.route("/a")
def a():
    Create_data()
    return "adasd"

@bp.route("/delete_borrower", methods=["GET", "POST"])
def delete_borrower():
    if request.method == "GET":
        name = request.args.get('name')
        Rborrower=Book_statusModel.query.filter_by(borrower_name=name).first() #查询用户的借阅信息
        if Rborrower:
            flash("该用户还有借阅记录，不可删除！！！")
            return redirect(url_for("base.borrowerInfo"))
        else:
            rs = BorrowerModel.query.filter_by(name=name).first()  # 查询该用户信息
            return render_template("delete_borrower.html",rs=rs)
    else:
        name = request.form["borrower_name"]
        rs =BorrowerModel.query.filter_by(name=name).first()  # 查询该用户信息
        db.session.delete(rs)
        db.session.commit()
        return redirect(url_for("base.borrowerInfo"))


@bp.route("/update_borrower", methods=["GET", "POST"])
def update_borrower():
    if request.method == "GET":
        name = request.args.get('name')
        # Rborrower=Book_statusModel.query.filter_by(borrower_name=name).first() #查询用户的借阅信息
        rs=BorrowerModel.query.filter_by(name=name).first()#查询该用户信息
        if rs:
            return render_template("update_borrower.html",rs=rs)
        else:
            return redirect(url_for("base.borrowerInfo"))

    else:
        name = request.form["borrower_name"]
        password = request.form["borrower_password"]
        phone =request.form["borrower_phone"]
        email = request.form["borrower_email"]
        # data = request.form.to_dict() #此方法和上面一样
        # name = data["borrower_name"]
        # password = data["borrower_password"]
        # phone = data["borrower_phone"]
        # email = data["borrower_email"]
        rs = BorrowerModel.query.filter_by(name=name).first()  # 查询该用户信息
        rs.password=password
        rs.phone=phone
        rs.email=email
        db.session.commit()
        return redirect(url_for("base.borrowerInfo"))

@bp.route("/borrowerInfo")
def borrowerInfo():
    results = BorrowerModel.query.all()
    if results:
        return render_template("borrowerInfo.html",results=results)
    else:
        flash("没有任何用户记录！！")
        return render_template("borrowerInfo.html")

@bp.route("/delete_book", methods=["GET", "POST"])
def delete_book():
    if request.method == "GET":
        return render_template("delete_book.html")
    else:
        Bid = request.form["book_id"]
        Rbook_status = Book_statusModel.query.filter_by(book_id=Bid).first()  # 查询该图书是否有借阅记录
        if Rbook_status:
            flash("该图书有未还记录，不可删除！！")
            return render_template("delete_book.html")
        else:
            Rbook = BookModel.query.filter_by(book_id=Bid).first()  # 查询该图书ID是否存在
            if Rbook:#图书存在
                db.session.delete(Rbook)
                db.session.commit()
                flash("图书已成功删除！！！")
                return render_template("delete_book.html")
            else:
                flash("图书不存在！！！")
                return render_template("delete_book.html")



@bp.route("/update_book", methods=["GET", "POST"])
def update_book():
    if request.method == "GET":
        return render_template("update_book.html")
    else:
        form = BookForm(request.form)
        if form.validate():
            Bid = form.id.data
            Rbook_status = Book_statusModel.query.filter_by(book_id=Bid).first()  # 查询该图书是否有借阅记录
            if Rbook_status:
                flash("该图书有未还记录，不可修改！！")
                return render_template("update_book.html")
            else:
                Rbook = BookModel.query.filter_by(book_id=Bid).first()  # 查询该图书ID是否存在
                if Rbook:  # 需要修改的图书存在
                    Rbook.book_id = Bid
                    Rbook.book_name = form.name.data
                    Rbook.book_author = form.author.data
                    Rbook.book_type = form.type.data
                    Rbook.book_publish = form.publish.data
                    Rbook.book_publish_time = form.publish_time.data
                    Rbook.book_price = form.price.data
                    Rbook.book_amount = form.amount.data
                    Rbook.book_stock = form.amount.data
                    db.session.commit()
                    flash("该图书已修改成功！！！")
                    return render_template("update_book.html")
                else:
                    flash("您想修改的图书不存在！！！")
                    return render_template("update_book.html")
        else:
            flash("您输入的信息不完整，请完善信息！")
            return render_template("update_book.html")


@bp.route("/input_book", methods=["GET", "POST"])
def input_book():
    if request.method == "GET":
        return render_template("input_book.html")
    else:
        form = BookForm(request.form)
        if form.validate():
            Bid = form.id.data
            Rbook = BookModel.query.filter_by(book_id=Bid).first()  # 查询该图书ID是否存在
            if Rbook:  # 图书信息不全匹配
                flash("该图书ID已存在，请修改您输入的图书信息！！")
                return render_template("input_book.html")
            else:  # 该图书ID不存在，则录入新的图书
                Bname = form.name.data
                Bauthor = form.author.data
                Btype = form.type.data
                Bpublish = form.publish.data
                Bpublish_time = form.publish_time.data
                Bprice = form.price.data
                Bamount = form.amount.data
                new_book = BookModel(book_id=Bid, book_name=Bname, book_author=Bauthor, book_type=Btype,
                                     book_publish=Bpublish,
                                     book_publish_time=Bpublish_time, book_price=Bprice,
                                     book_amount=Bamount, book_stock=Bamount)
                db.session.add(new_book)
                db.session.commit()
                flash("新图书已入库！！！")
                return render_template("input_book.html")
        else:  # 表单检验不通过，返回入库界面
            flash("您输入的信息不完整，请完善信息！")
            return render_template("input_book.html")


@bp.route("/return_book", methods=["GET", "POST"])
def return_book():
    if request.method == "GET":
        return render_template("return_book.html")
    else:
        id_book = request.form["book_id"]
        if g.user.type == "admin":  # 判断当前登录的用户类型
            name_borrower = request.form["borrower_name"]  # 该字段是用于管理员帮学生借阅图书使用
        else:  # 学生登录，将借阅者姓名赋值为当前登录的用户名
            name_borrower = g.user.name
        Rbook = BookModel.query.filter_by(book_id=id_book).first()
        Rborrower = Book_statusModel.query.filter_by(borrower_name=name_borrower,
                                                     book_id=id_book).first()  # 查询用户是否借阅该图书
        if Rborrower:  # 存在
            db.session.delete(Rborrower)  # 删除在book_status表的这条信息
            Rbook.book_stock = Rbook.book_stock + 1  # 将种图书库存 +1
            db.session.commit()
            flash("归还图书成功！！！")
            return render_template("return_book.html")
        else:  # 没有借书记录
            flash("没有该借阅者的借书记录")
            return render_template("return_book.html")


@bp.route("/borrow_book", methods=["GET", "POST"])
def borrow_book():
    if request.method == "GET":
        return render_template("borrow_book.html")
    else:
        id_book = request.form["book_id"]
        if g.user.type == "admin":  # 判断当前登录的用户类型
            name_borrower = request.form["borrower_name"]  # 该字段是用于管理员帮学生借阅图书使用
            if name_borrower == g.user.name:  # 判断输入的借书证号（用户名）是否为管理员类型
                flash("该系统尚未完善管理员借阅图书的功能。。。")
                return render_template("borrow_book.html")
        else:  # 学生登录，将借阅者姓名赋值为当前登录的用户名
            name_borrower = g.user.name

        Rbook = BookModel.query.filter_by(book_id=id_book).first()  # 查询是否有该图书
        Rborrower = Book_statusModel.query.filter_by(borrower_name=name_borrower,
                                                     book_id=id_book).first()  # 查询用户是否借阅该图书
        if Rbook:
            if Rborrower:
                flash("您已借阅过该图书，不可再借阅一本！！")
                return render_template("borrow_book.html")
            else:
                new_borrow = Book_statusModel(book_id=id_book, book_name=Rbook.book_name, borrower_name=name_borrower)
                db.session.add(new_borrow)
                Rbook.book_stock = Rbook.book_stock - 1  # 将种图书库存 -1
                db.session.commit()
                flash("借书成功！！！")
                return render_template("borrow_book.html")
        else:
            flash("您想借阅的图书，书库没藏有！！！")
            return render_template("borrow_book.html")


@bp.route("query_borrowedInfo", methods=["GET", "POST"])
def query_borrowedInfo():
    if request.method == "GET":
        return render_template("query_borrowedInfo.html")
    else:
        if g.user.type == "admin":
            results = rs_borrowedInfo()
            if results:
                return render_template("show_borrowedInfo.html", results=results)
            else:
                flash("您查询的借阅信息不存在！！")
                return render_template("query_borrowedInfo.html")
        else:
            results = Book_statusModel.query.filter(
                Book_statusModel.borrower_name.like("%" + g.user.name)).all()
            # print(results)
            if results:
                return render_template("show_borrowedInfo.html", results=results)
            else:
                flash("您没有借阅图书！！")
                return render_template("query_borrowedInfo.html")


def rs_borrowedInfo():
    id_book = request.form["book_id"]
    name_book = request.form["book_name"]
    name_borrower = request.form["borrower_name"]
    start_time = request.form["start_time"]
    end_time = request.form["end_time"]
    if start_time == "":
        start_time = "1970-01-01"
    if end_time == "":
        end_time = "2099-12-31"
    results = Book_statusModel.query.filter(
        Book_statusModel.book_id.like("%" + id_book) if id_book is not None else text(""),
        Book_statusModel.book_name.like("%" + name_book) if name_book is not None else text(""),
        Book_statusModel.borrower_name.like("%" + name_borrower) if name_borrower is not None else text(""),
        Book_statusModel.borrow_time >= start_time,
        Book_statusModel.borrow_time <= end_time
    ).all()
    # print(results)
    return results


@bp.route("/query_Book", methods=["GET", "POST"])
def query_Book():
    if request.method == "GET":
        return render_template("query_Book.html")  # GET请求返回查询图书界面
    else:
        results = rsBook()
        if results:
            return render_template('show_Book.html', results=results)  # 将结果返回图书展示界面
        else:
            flash("您查询的书籍不存在！！")
            return render_template('query_Book.html')  # 图书不存在，重新返回图书查询界面，并把通知信息返回


def rsBook():  # 查询图书
    name = request.form["name"]
    author = request.form["author"]
    publish = request.form["publish"]
    start_time = request.form["start_time"]
    end_time = request.form["end_time"]
    Btype = request.form["type"]
    if start_time == "":
        start_time = 0
    if end_time == "":
        end_time = 9999
    results = BookModel.query.filter(
        BookModel.book_name.like('%' + name) if name is not None else text(""),
        BookModel.book_type.like('%' + Btype) if Btype is not None else text(""),
        BookModel.book_author.like('%' + author) if author is not None else text(""),
        BookModel.book_publish.like('%' + publish) if publish is not None else text(""),
        BookModel.book_publish_time >= start_time,
        BookModel.book_publish_time <= end_time
    ).all()
    return results
